"""Add extended product tables - specifications, attributes, warranty, shipping, services

Revision ID: a289be90b234
Revises: 9b684b4bacb8
Create Date: 2025-10-05 12:24:20.984108

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'a289be90b234'
down_revision: Union[str, Sequence[str], None] = '9b684b4bacb8'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('product_attributes',
    sa.Column('id', sa.Integer(), nullable=False, comment='属性ID'),
    sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('attr_type', sa.String(length=50), nullable=False, comment='属性类型：color, size, capacity等'),
    sa.Column('attr_value', sa.String(length=100), nullable=False, comment='属性值'),
    sa.Column('attr_name', sa.String(length=100), nullable=False, comment='显示名称'),
    sa.Column('price_adjustment', sa.Numeric(precision=10, scale=2), nullable=True, comment='价格调整'),
    sa.Column('stock_quantity', sa.Integer(), nullable=True, comment='该属性组合的库存'),
    sa.Column('is_default', sa.Boolean(), nullable=True, comment='是否默认选项'),
    sa.Column('sort_order', sa.Integer(), nullable=True, comment='排序顺序'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_attributes_id'), 'product_attributes', ['id'], unique=False)
    op.create_table('product_services',
    sa.Column('id', sa.Integer(), nullable=False, comment='服务ID'),
    sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('service_type', sa.String(length=100), nullable=False, comment='服务类型'),
    sa.Column('service_desc', sa.Text(), nullable=False, comment='服务说明'),
    sa.Column('service_period', sa.String(length=50), nullable=True, comment='服务期限'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.Column('sort_order', sa.Integer(), nullable=True, comment='排序顺序'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_services_id'), 'product_services', ['id'], unique=False)
    op.create_table('product_shipping',
    sa.Column('id', sa.Integer(), nullable=False, comment='物流ID'),
    sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('weight', sa.Numeric(precision=8, scale=2), nullable=True, comment='重量(kg)'),
    sa.Column('length', sa.Numeric(precision=8, scale=2), nullable=True, comment='长度(cm)'),
    sa.Column('width', sa.Numeric(precision=8, scale=2), nullable=True, comment='宽度(cm)'),
    sa.Column('height', sa.Numeric(precision=8, scale=2), nullable=True, comment='高度(cm)'),
    sa.Column('package_type', sa.String(length=100), nullable=True, comment='包装类型'),
    sa.Column('package_contents', sa.Text(), nullable=True, comment='包装清单'),
    sa.Column('shipping_fee', sa.Numeric(precision=10, scale=2), nullable=True, comment='运费'),
    sa.Column('free_shipping_limit', sa.Numeric(precision=10, scale=2), nullable=True, comment='包邮门槛'),
    sa.Column('shipping_method', sa.String(length=100), nullable=True, comment='配送方式'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_shipping_id'), 'product_shipping', ['id'], unique=False)
    op.create_table('product_specifications',
    sa.Column('id', sa.Integer(), nullable=False, comment='规格ID'),
    sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('spec_name', sa.String(length=100), nullable=False, comment='规格名称'),
    sa.Column('spec_value', sa.String(length=500), nullable=False, comment='规格值'),
    sa.Column('spec_group', sa.String(length=50), nullable=True, comment='规格分组'),
    sa.Column('sort_order', sa.Integer(), nullable=True, comment='排序顺序'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_specifications_id'), 'product_specifications', ['id'], unique=False)
    op.create_table('product_warranty',
    sa.Column('id', sa.Integer(), nullable=False, comment='保修ID'),
    sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('warranty_type', sa.String(length=100), nullable=False, comment='保修类型'),
    sa.Column('warranty_period', sa.String(length=50), nullable=False, comment='保修期限'),
    sa.Column('warranty_desc', sa.Text(), nullable=True, comment='保修说明'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.Column('sort_order', sa.Integer(), nullable=True, comment='排序顺序'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_warranty_id'), 'product_warranty', ['id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_product_warranty_id'), table_name='product_warranty')
    op.drop_table('product_warranty')
    op.drop_index(op.f('ix_product_specifications_id'), table_name='product_specifications')
    op.drop_table('product_specifications')
    op.drop_index(op.f('ix_product_shipping_id'), table_name='product_shipping')
    op.drop_table('product_shipping')
    op.drop_index(op.f('ix_product_services_id'), table_name='product_services')
    op.drop_table('product_services')
    op.drop_index(op.f('ix_product_attributes_id'), table_name='product_attributes')
    op.drop_table('product_attributes')
    # ### end Alembic commands ###
